﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ChinaDynastyTours.Models;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace ChinaDynastyTours.Controls
{
    class UserInfoCtrl
    {
        #region 获取系统用户及相关信息
        
        //internal static string[] GetAllUserName()
        //{
        //    string[] userNames;
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"SELECT [Name] FROM aspnet_UserInfo");
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //    {
        //        userNames = new string[ds.Tables[0].Rows.Count];
        //        for(int i=0;i<ds.Tables[0].Rows.Count;i++)
        //        {
        //            userNames[i] = ds.Tables[0].Rows[i]["Name"].ToString();
        //        }
        //        return userNames;
        //    }
        //    else
        //        return null;
        //}
        /// <summary>
        /// 根据UserName获取真实姓名
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static string GetNameByUserName(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [Name] FROM aspnet_UserInfo WHERE UserName='{0}'", username);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 根据真实姓名获取电话、传真
        /// </summary>
        /// <param name="realname"></param>
        /// <returns></returns>
        //internal static DataTable GetTelephoneFaxByUserRealName(string realname)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"SELECT [Telephone],[Fax] FROM aspnet_UserInfo WHERE Name='{0}'", realname);
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 通过用户真实姓名，获取EMail地址，由于前端的用户名与用户姓名的添加、修改控制了唯一性(真实姓名)，所以可以通过真实姓名获取EMail
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static string GetEMailAddressByName(string name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT EMail FROM aspnet_UserInfo WHERE Name='{0}'", name);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 检测是否已经存在同姓名的人
        /// </summary>
        /// <param name="realname">真实姓名</param>
        /// <returns></returns>
        //internal static bool ExistRealName(string realname)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"SELECT [Name] FROM aspnet_UserInfo WHERE [Name]='{0}'", realname);
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);

        //    return (db.ExecuteScalar(dbCommand) != null);
        //}

        /// <summary>
        /// 根据UserName获取个人相关信息
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        //internal static DataTable GetUserInfoData(string username)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    //为增加用户'通知’信息，注释掉下面一行，获取全部信息
        //    //string sql = string.Format(@"SELECT [UserInfoID], [UserName], [IdentificationCardNo], [Name], [Telephone], [Mobile], [Fax], [EMail], [Remark],[DepartmentID],[RankID] FROM aspnet_UserInfo WHERE UserName='{0}'", username);
        //    string sql = string.Format(@"SELECT * FROM aspnet_UserInfo WHERE UserName='{0}'", username);
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 获取全部系统用户的'登录用户名'及'姓名'信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetUserInfoData()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = "SELECT [UserInfoID], [UserName], [Name] FROM aspnet_UserInfo ui,bas_Department dp where ui.DepartmentID = dp.DepartmentID and dp.DepartmentName <> '导游部'";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                DataRow dr = ds.Tables[0].NewRow();
                dr["Name"] = "";
                ds.Tables[0].Rows.Add(dr);
                return ds.Tables[0];
            }
            else
                return null;
        }

        /// <summary>
        /// 根据用户名获取用户所属级别，总经理,部门领导,普通员工
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        //internal static int GetUserInfoForRankID(string username)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"SELECT [RankID] FROM aspnet_UserInfo WHERE UserName='{0}'",username);
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);

        //    object obj = db.ExecuteScalar(dbCommand);
        //    if (obj != null)
        //        return int.Parse(obj.ToString());
        //    else
        //        return 0;//返回最高级别，总经理
        //}
        /// <summary>
        /// 根据用户名获取用户所属部门
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        //internal static string GetUserInfoForDepartmentID(string username)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string sql = string.Format(@"SELECT [DepartmentID] FROM aspnet_UserInfo WHERE UserName='{0}'", username);
        //    DbCommand dbCommand = db.GetSqlStringCommand(sql);

        //    object obj = db.ExecuteScalar(dbCommand);
        //    if (obj != null)
        //        return obj.ToString();
        //    else
        //        return "";//返回0所属的部门，不知是谁，请根据数据表核对
        //}

        #endregion

        #region 系统登录用户的基本信息增、删、改

//        internal static int  CreateUserInfoData(UserInfoEntity entity)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            string sql = string.Format(@"INSERT INTO aspnet_UserInfo ([UserName], [IdentificationCardNo], [Name], [Telephone], [Mobile], [Fax], [EMail], [Remark],[DepartmentID],[RankID])
//                                         VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},{9})",
//                                        entity.UserName,
//                                        entity.IdentificationCardNo,
//                                        entity.Name,
//                                        entity.Telephone,
//                                        entity.Mobile,
//                                        entity.Fax,
//                                        entity.EMail,
//                                        entity.Remark,
//                                        entity.DepartmentID,
//                                        entity.Rank);
//            return db.ExecuteNonQuery(CommandType.Text, sql);

//        }
//        internal static int UpdateUserInfo(UserInfoEntity entity)
//        {
//            Database db = DatabaseFactory.CreateDatabase();
//            string sql = string.Format(@"UPDATE aspnet_UserInfo SET [IdentificationCardNo]='{0}', [Name]='{1}', [Telephone]='{2}', [Mobile]='{3}', [Fax]='{4}', [EMail]='{5}', [Remark]='{6}',[DepartmentID]={7},[RankID]={8} 
//                                         WHERE [UserName]='{9}'",
//                                         entity.IdentificationCardNo,
//                                         entity.Name,
//                                         entity.Telephone,
//                                         entity.Mobile,
//                                         entity.Fax,
//                                         entity.EMail,
//                                         entity.Remark,
//                                         entity.DepartmentID,
//                                         entity.Rank,
//                                         entity.UserName);

//            return db.ExecuteNonQuery(CommandType.Text, sql);
//        }
//        public static int DeleteUserInfo(string username)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            string sql = string.Format(@"DELETE FROM aspnet_UserInfo WHERE UserName='{0}'",username);
//            return db.ExecuteNonQuery(CommandType.Text, sql);
//        }

        #endregion
    }
}
